<?php
/**
 *
 * User: 梁晓伟  lxw11109@gmail.com
 * Date: 2017-11-05
 * Time: 16:37
 */

namespace App\Http\Controllers\Api\V1\Manager\Excel;


use App\Http\Controllers\Controller;
use App\Models\Activity;
use App\Models\UserManagerLog;
use App\Models\ActivityOrder;
use App\Models\User;
use App\Models\UserAddition;
use DateTime;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;

class ActivityExcelController extends Controller
{
    public function activityExcel(Request $request)
    {
        $file = $request->file('file');
        if ($file->isValid()) {

            $entension = $file->getClientOriginalExtension();
            $newName = time() . '.' . $entension;
            $file->move(public_path('uploads/excel'), $newName);    //把缓存文件移动到制定文件夹

            Excel::load(public_path("uploads/excel/" . $newName), function ($reader) use (&$res) {
                $reader = $reader->getSheet(0);
                $res = $reader->toArray();
            });
            @unlink(public_path("uploads/excel/" . $newName));
            unset($res[0]);
            $userAddition = [];
            $order = [];
            try {
                \DB::beginTransaction();
                $j = 0;
                for ($i = 1; $i <= count($res); $i++) {
                    //判断用户是否存在
                    $user = User::where('certificatesNumber', $res[$i][15])->first();
                    $sex = str_replace(array("男", "女"), array('1', '2', '0'), $res[$i][11]);
                    if ($res[$i][2]) {
                        $level = str_replace(array("高级会员", "普通会员"), array('SERIOR', 'GENERAL'), $res[$i][2]);
                    } else {
                        $level = "GENERAL";
                    }
                    if (!$level) {
                        $level = "GENERAL";
                    }
                    $res[$i][13] = date("Y-m-d", strtotime($res[$i][13]));

                    if ($user) {
                        $userId = $user['userId'];
                        User::where('userId', $userId)->update(['userName' => $res[$i][10], 'sex' => $sex, 'level' => $level, 'leadingInStatus' => "USER", 'birthday' => $res[$i][13],
                            'phone' => $res[$i][12],'activityStatus'=>1]);
                    } else {
                        $accessToken = encrypt($res[$i][12] . time());
                        $userId = User::insertGetId(['userName' => $res[$i][10], 'sex' => $sex, 'level' => $level, 'leadingInStatus' => "USER", 'birthday' => $res[$i][13],
                            'certificatesStatus' => 'CARDS', 'certificatesNumber' => $res[$i][15], 'phone' => $res[$i][12], 'accessToken' => $accessToken,'activityStatus'=>1]);
                        if (!$userId) {
                            throw new \Exception("插入用户失败");
                        }
                    }
                    if ($res[$i][9]) {
                        $userAddition[$j]['userId'] = $userId;
                        $userAddition[$j]['fieldsId'] = 88;
                        $userAddition[$j]['value'] = $res[$i][9];
                        $j++;
                    }
                    if ($res[$i][14]) {
                        $userAddition[$j]['userId'] = $userId;
                        $userAddition[$j]['fieldsId'] = 21;
                        $userAddition[$j]['value'] = $res[$i][14];
                        $j++;
                    }
                    if ($res[$i][16]) {
                        $userAddition[$j]['userId'] = $userId;
                        $userAddition[$j]['fieldsId'] = 12;
                        $userAddition[$j]['value'] = $res[$i][16];
                        $j++;
                    }
                    if ($res[$i][17]) {
                        $userAddition[$j]['userId'] = $userId;
                        $userAddition[$j]['fieldsId'] = 34;
                        $userAddition[$j]['value'] = $res[$i][17];
                        $j++;
                    }
                    if ($res[$i][18]) {
                        $userAddition[$j]['userId'] = $userId;
                        $userAddition[$j]['fieldsId'] = 72;
                        $userAddition[$j]['value'] = $res[$i][18];
                        $j++;
                    }
                    if ($res[$i][19]) {
                        $userAddition[$j]['userId'] = $userId;
                        $userAddition[$j]['fieldsId'] = 95;
                        $userAddition[$j]['value'] = $res[$i][19];
                        $j++;
                    }
                    if ($res[$i][20]) {
                        $userAddition[$j]['userId'] = $userId;
                        $userAddition[$j]['fieldsId'] = 10;
                        $userAddition[$j]['value'] = $res[$i][20];
                        $j++;
                    }

                    $activity = Activity::select("activityId")->where('activityCode', $res[$i][7])->first();
                    if (!$activity) {
                        throw new \Exception("请先插入活动");
                    }

                    $information[0]['id'] = 88;
                    $information[0]['key'] = "学号";
                    $information[0]['value'] = $res[$i][9];
                    $information[1]['id'] = 21;
                    $information[1]['key'] = "目前住址";
                    $information[1]['value'] = $res[$i][14];
                    $information[2]['id'] = 72;
                    $information[2]['key'] = "学历";
                    $information[2]['value'] = $res[$i][16];
                    $information[3]['id'] = 34;
                    $information[3]['key'] = "职务";
                    $information[3]['value'] = $res[$i][17];
                    $information[4]['id'] = 72;
                    $information[4]['key'] = "职业";
                    $information[4]['value'] = $res[$i][18];
                    $information[5]['id'] = 95;
                    $information[5]['key'] = "获取信息渠道";
                    $information[5]['value'] = $res[$i][19];
                    $information[6]['id'] = 10;
                    $information[6]['key'] = "备注";
                    $information[6]['value'] = $res[$i][20];
                    $time = explode("至", $res[$i][4]);
                    $startTime = date("Y-m-d", strtotime(@$time[0]));
                    $endTime = date("Y-m-d", strtotime(@$time[1]));
                    //插入订单
                    $order[$i - 1]['additionalInformation'] = serialize($information);
                    $order[$i - 1]['orderNo'] = time() . rand(1000, 9000);
                    $order[$i - 1]['activityId'] = $activity['activityId'];
                    $order[$i - 1]['userId'] = $userId;
                    $order[$i - 1]['sex'] = $sex;
                    $order[$i - 1]['phone'] = $res[$i][12];
                    $order[$i - 1]['certificatesStatus'] = 'CARDS';
                    $order[$i - 1]['certificatesNumber'] = $res[$i][15];
                    $order[$i - 1]['orderStatus'] = 'ORDERFINISH';
                    $order[$i - 1]['userLevel'] = $level;
                    $order[$i - 1]['activityCode'] = $res[$i][7];
                    $order[$i - 1]['activityCode'] = $res[$i][7];
                    $order[$i - 1]['createTime'] = date("Y-m-d", time());
                    $order[$i - 1]['payTime'] = $startTime;
                    $order[$i - 1]['finishTime'] = $endTime;
                    $order[$i - 1]['contractorAllName'] = $res[$i][7];
                }
                $status = ActivityOrder::insert($order);
                if (!$status) {
                    throw new \Exception("创建活动订单失败");
                }
                $sql = "INSERT INTO `user_addition`(`userId`,`fieldsId`,`value`) VALUES";
                foreach ($userAddition as $value) {
                    $userId = $value['userId'];
                    $fieldsId = $value['fieldsId'];
                    $value = $value['value'] . "";

                    $sql .= "($userId, $fieldsId,'$value'),";
                }
                if ($userAddition) {
                    $sql = substr($sql, 0, strlen($sql) - 1);
                    $sql .= " ON DUPLICATE  KEY  UPDATE `value` = VALUES(`value`)";
                    $status = \DB::insert($sql);
                    if (!$status) {
                        throw new \Exception("插入用户附加属性失败");
                    }
                }
                \DB::commit();
                return $this->apiResponse('');
            } catch (\Exception $e) {
                \DB::rollback();
                \Log::error("ActivityExcelController activityExcel error message:" . $e->getMessage());
                return $this->apiResponse('', [
                    'errorCode' => "11011",
                    'errorMessage' => $e->getMessage()
                ]);
            }
        } else {
            return $this->apiResponse('', config('errorCode.UPLOAD_FILE_ERROR'));
        }
    }

    //活动报名表
    public function activityexport(Request $request)
    {
        $activityId = $request->activityId;
        //$activityId = 7;
        $activity = \DB::table('activity_order')
            ->join('user', 'activity_order.userId', '=', 'user.userId')
            ->join('activity', 'activity_order.activityId', '=', 'activity.activityId')
            ->select('activity_order.*', 'user.*', 'activity.activityName', 'activity.activityNumber','activity.province as activityProvince','activity.city as activityCity',
                'activity.area as activityArea','activity.city as activityDetail')->where('activity_order.activityId', '=', $activityId)
            ->where('activity.activityId',$activityId)
            ->whereIn('activity_order.orderStatus',array("ORDERFINISH","ENTRYING"))
            ->get();
        $activity = json_decode($activity, true);
        foreach ($activity as $key => $value) {
            $activity[$key]['additionalInformation'] = unserialize($activity[$key]['additionalInformation']);
        }
        foreach ($activity as $key => &$value) {
            $value['x_number'] = '';      //学号
            $value['province_city'] = '';    //所在省市
            $value['xueli'] = '';             //学历
            $value['zhiwu'] = '';
            $value['zhiye'] = '';            //职业
            $value['huoqu'] = '';            //获取信息渠道
            $value['beizhu'] = '';            //备注
            $value['zhiwei'] = ''; //职位
            $value['level'] = str_replace(array("GENERAL", "SERIOR", "ACCURATE_SENIOR"), array("普通会员", "高级会员", "普通会员"), $value['level']);
            $value['sex'] = str_replace(array("1", "2", "0"), array("男", "女", "未知"), $value['sex']);
            if (count($value['additionalInformation']) > 0)
                foreach ($activity[$key]['additionalInformation'] as $k => $v) {
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 88) {
                        $value['x_number'] = $v['value'];
                    }
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 109) {
                        $value['province_city'] = $v['value'];
                    }
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 38) {
                        $value['xueli'] = $v['value'];
                    }
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 34) {
                        $value['zhiwu'] = $v['value'];
                    }//
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 72) {
                        $value['zhiye'] = $v['value'];
                    }
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 95) {
                        $value['huoqu'] = $v['value'];
                    }
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 10) {
                        $value['beizhu'] = $v['value'];
                    }
                    if ($activity[$key]['additionalInformation'][$k]['id'] == 29) {
                        $value['zhiwei'] = $v['value'];
                    }

                }
        }
        $data = [['活动报名表', '总序号', '用户身份', '课程/活动名称', '课程/活动地点', '课程编号', '关联号', '序号', '学号', '姓名', '性别', '手机号码', '出生日期',
            '所在省市', '身份证号码', '学历', '职务/职位', '职业', '获取信息渠道', '备注'],];
        $i = 0;
        foreach ($activity as $key => &$value) {
            $data[$i + 1] = ['', $i + 1, $value['level'], $value['activityName'],  $value['activityProvince']. $value['activityCity']. $value['activityArea'].$value['activityDetail'], $value['activityNumber'], $value['activityCode'], $i+1,
                $value['x_number'], $value['userName'], $value['sex'], $value['phone'], date("Y-m-d",strtotime($value['birthday'])), $value['province']. $value['city']. $value['area'].$value['detail'], "'".$value['certificatesNumber'],
                $value['xueli'], $value['zhiwu'].$value['zhiwei'], $value['zhiye'], $value['huoqu'],$value['beizhu']];
            $i++;
        }
        $managerId = $request->managerId;
        $userName = \DB::table('user_manager')->select('userName')->where('managerId', '=', $managerId)->first();
        if ($userName) {
            $userName = $userName->userName;
        }

        $data[$i + 1] = ["管理员名字：" . $userName, "导出时间：" . date("Y-m-d H:i:s", time())];
        //  $sheet->setFontFamily('宋体');
        $status = Excel::create($value['activityName'] . "活动报名表", function ($excel) use ($data) {
            $excel->sheet('score', function ($sheet) use ($data) {
                $sheet->setFontFamily('宋体');
                $sheet->rows($data);

            });
        });
        if ($status) {
            $ip = getIp();
            UserManagerLog::create(['ip' => $ip, 'operation' => $value['activityName'] . "活动报名excel表导出成功", 'userName' => $userName, 'managerId' => $managerId]);
            $status->export('xls');
            return $this->apiResponse($status);
        } else {
            return $this->apiResponse('', config('errorCode.MANAGER_LOGIN_FAILED'));
        }
    }

    /**
     * 活动订单列表导出
     */
    public function activityOrder(Request $request)
    {
        $starttime = $request->startTime;
        $finishtime = $request->finishtime;
        $sql = \DB::table('activity_order')
            ->join('user', 'activity_order.userId', '=', 'user.userId')
            ->join('activity', 'activity_order.activityId', '=', 'activity.activityId')
            ->select('user.userName', 'user.phone', 'activity_order.userLevel', 'activity.activityName', 'activity.province', 'activity.city', 'activity.area', 'activity.activityPrice',
                'activity.startTime', 'activity.endTime', 'activity_order.payTime', 'activity_order.orderStatus', 'activity_order.createTime','activity_order.totalPrice');
        if ($starttime) {
            $sql->where('activity.endTime', ">=", date("Y-m-d", strtotime($starttime)));
        }
        if ($finishtime) {
            $sql->where('activity.endTime', "<=", date("Y-m-d", strtotime($finishtime)));
        }
        $activityorder1 = $sql->get();

        //return $activityorder;
        $activityorder = json_decode($activityorder1, true);
        foreach ($activityorder as &$value) {
            if ($value['orderStatus'] == 'NONPAYMENT') {
                $value['orderStatus'] = '待付款';
            }
            if ($value['orderStatus'] == 'OVERDUE') {
                $value['orderStatus'] = '订单过期';
            }
            if ($value['orderStatus'] == 'PARPAYMENT') {
                $value['orderStatus'] = '部分退款';
            }
            if ($value['orderStatus'] == 'ORDERFINISH') {
                $value['orderStatus'] = '交易完成';
            }
            if ($value['orderStatus'] == 'WAITREFUND') {
                $value['orderStatus'] = '待退款';
            }
            if ($value['orderStatus'] == 'REFUNDEND') {
                $value['orderStatus'] = '退款成功';
            }
            if ($value['orderStatus'] == 'FAIL') {
                $value['orderStatus'] = '失败';
            }
            if ($value['orderStatus'] == 'ENTRYING') {
                $value['orderStatus'] = '待入场';
            }
            if ($value['orderStatus'] == 'CANCEL') {
                $value['orderStatus'] = '取消订单';
            }

            if ($value['userLevel'] == 'SERIOR') {
                $value['userLevel'] = '高级会员';
            }
            if ($value['userLevel'] == 'GENERAL') {
                $value['userLevel'] = '普通会员';
            }
            if ($value['userLevel'] == 'ACCURATE_SENIOR') {
                $value['userLevel'] = '准高级会员';
            }
        }
        $data = [['姓名', '手机号码', '会员身份', '活动名称', '活动地址', '报名时间', '活动时间', '活动费用', '状态'],];
        $i = 0;
        foreach ($activityorder as $key => &$value) {
            $data[$i + 1] = [$value['userName'], $value['phone'], $value['userLevel'], $value['activityName'], $value['province'] . $value['city'], $value['createTime'],
                $value['startTime'] . '至' . $value['endTime'], $value['totalPrice'] / 100, $value['orderStatus']];
            $i++;
        }
        $managerId = $request->managerId;
        $userName = \DB::table('user_manager')->select('userName')->where('managerId', '=', $managerId)->first();

        $managerName = $userName->userName;
        $data[$i + 1] = ["管理员名字：" . $managerName, "导出时间：" . date("Y-m-d H:i:s", time())];
        //  $sheet->setFontFamily('宋体');
        $status = Excel::create("活动订单表", function ($excel) use ($data) {
            $excel->sheet('score', function ($sheet) use ($data) {
                $sheet->setFontFamily('宋体');
                $sheet->rows($data);
            });
        });
        if ($status) {
            $ip = getIp();
            UserManagerLog::create(['ip' => $ip, 'operation' => "活动订单表", 'userName' => $managerName, 'managerId' => $managerId]);
            $status->export('xls');
            return $this->apiResponse($status);
        } else {
            return $this->apiResponse('', config('errorCode.MANAGER_LOGIN_FAILED'));
        }

    }
}